package com.aquarium.data;

import java.util.ArrayList;

import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.aquarium.config.DosingPumpData;
import com.aquarium.config.ModuleData;
import com.aquarium.config.ProteinSkimmerData;
import com.aquarium.config.UVLightData;
import com.aquarium.config.WaterMotorData;

public class ModuleSettingDataBase extends SQLiteOpenHelper {

	private final static String TAG = "ModuleSettingDataBase";
	private static final int VERSION = 1;
	private static final String DATABASE_NAME = "module_setting_database";

	public static final String WATER_MOTOR_TABLE = "WATER_MOTOR_TABLE";
	public static final String PROTEIN_SKIMMER_TABLE = "PROTEIN_SKIMMER_TABLE";
	public static final String UV_LIGHT_TABLE = "UV_LIGHT_TABLE";
	public static final String DOSING_PUMP_TABLE = "DOSING_PUMP_TABLE";

	public static final String AM_PM = "am_pm";
	public static final String HOUR = "hour";
	public static final String MINUTE = "minute";
	public static final String MON = "mon";
	public static final String TUES = "tues";
	public static final String WED = "wed";
	public static final String THUR = "thur";
	public static final String FRI = "fri";
	public static final String SAT = "sat";
	public static final String SUN = "sun";
	public static final String DURATION = "duration";
	public static final String ON_OFF = "on_off";
	public static final String PUMP_1_ON_OFF = "pump_1_on_off";
	public static final String PUMP_2_ON_OFF = "pump_2_on_off";
	public static final String PUMP_3_ON_OFF = "pump_3_on_off";
	public static final String PUMP_4_ON_OFF = "pump_4_on_off";
	public static final String PUMP_1_DOSING = "pump_1_dosing";
	public static final String PUMP_2_DOSING = "pump_2_dosing";
	public static final String PUMP_3_DOSING = "pump_3_dosing";
	public static final String PUMP_4_DOSING = "pump_4_dosing";

	private static ModuleSettingDataBase mModuleSettingDataBase;
	private SQLiteDatabase mSqLiteDatabase;

	private ModuleSettingDataBase(Context context) {
		super(context, DATABASE_NAME, null, VERSION);
		mSqLiteDatabase = getWritableDatabase();
	}

	public static ModuleSettingDataBase getInstance(Context context) {
		if (mModuleSettingDataBase == null) {
			mModuleSettingDataBase = new ModuleSettingDataBase(context);
		}
		return mModuleSettingDataBase;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + WATER_MOTOR_TABLE + " ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + HOUR
				+ " INTEGER, " + MINUTE + " INTEGER, " + AM_PM + " INTEGER, "
				+ ON_OFF + " INTEGER, " + MON + " INTEGER, " + TUES
				+ " INTEGER, " + WED + " INTEGER, " + THUR + " INTEGER, " + FRI
				+ " INTEGER, " + SAT + " INTEGER, " + SUN + " INTEGER" + ");");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + PROTEIN_SKIMMER_TABLE + " ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + HOUR
				+ " INTEGER, " + MINUTE + " INTEGER, " + AM_PM + " INTEGER, "
				+ DURATION + " INTEGER, " + MON + " INTEGER, " + TUES
				+ " INTEGER, " + WED + " INTEGER, " + THUR + " INTEGER, " + FRI
				+ " INTEGER, " + SAT + " INTEGER, " + SUN + " INTEGER" + ");");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + UV_LIGHT_TABLE + " ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + HOUR
				+ " INTEGER, " + MINUTE + " INTEGER, " + AM_PM + " INTEGER, "
				+ MON + " INTEGER, " + TUES + " INTEGER, " + WED + " INTEGER, "
				+ THUR + " INTEGER, " + FRI + " INTEGER, " + SAT + " INTEGER, "
				+ SUN + " INTEGER" + ");");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + DOSING_PUMP_TABLE + " ("
				+ "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + HOUR
				+ " INTEGER, " + MINUTE + " INTEGER, " + AM_PM + " INTEGER, "
				+ PUMP_1_ON_OFF + " INTEGER, " + PUMP_2_ON_OFF + " INTEGER, "
				+ PUMP_3_ON_OFF + " INTEGER, " + PUMP_4_ON_OFF + " INTEGER, "
				+ PUMP_1_DOSING + " INTEGER, " + PUMP_2_DOSING + " INTEGER, "
				+ PUMP_3_DOSING + " INTEGER, " + PUMP_4_DOSING + " INTEGER, "
				+ MON + " INTEGER, " + TUES + " INTEGER, " + WED + " INTEGER, "
				+ THUR + " INTEGER, " + FRI + " INTEGER, " + SAT + " INTEGER, "
				+ SUN + " INTEGER" + ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int i, int j) {
		onCreate(db);
	}

	public boolean insertWaterMotorData(WaterMotorData data) {
		ContentValues values = createModuleData(data);
		try {
			mSqLiteDatabase.insert(WATER_MOTOR_TABLE, null, values);
		} catch (Exception e) {
			Log.e(TAG, "add note item error" + e.toString());
			return false;
		}
		return true;
	}

	public boolean insertProteinSkimmerData(ProteinSkimmerData data) {
		ContentValues values = createModuleData(data);
		try {
			mSqLiteDatabase.insert(PROTEIN_SKIMMER_TABLE, null, values);
		} catch (Exception e) {
			Log.e(TAG, "add note item error" + e.toString());
			return false;
		}
		return true;
	}

	public boolean insertUVLightData(UVLightData data) {
		ContentValues values = createModuleData(data);
		try {
			mSqLiteDatabase.insert(UV_LIGHT_TABLE, null, values);
		} catch (Exception e) {
			Log.e(TAG, "add note item error" + e.toString());
			return false;
		}
		return true;
	}

	public boolean insertDosingPumpData(DosingPumpData data) {
		ContentValues values = createModuleData(data);
		try {
			mSqLiteDatabase.insert(DOSING_PUMP_TABLE, null, values);
		} catch (Exception e) {
			Log.e(TAG, "add note item error" + e.toString());
			return false;
		}
		return true;
	}

	public void deleteWaterMotorData(int position) {
		deleteData(WATER_MOTOR_TABLE, position);
	}

	public void deleteWaterMotorData(ArrayList<Integer> positions) {
		deleteData(WATER_MOTOR_TABLE, positions);
	}

	public void deleteProteinSkimmerData(int position) {
		deleteData(PROTEIN_SKIMMER_TABLE, position);
	}

	public void deleteProteinSkimmerData(ArrayList<Integer> positions) {
		deleteData(PROTEIN_SKIMMER_TABLE, positions);
	}

	public void deleteUVLightData(int position) {
		deleteData(UV_LIGHT_TABLE, position);
	}

	public void deleteUVLightData(ArrayList<Integer> positions) {
		deleteData(UV_LIGHT_TABLE, positions);
	}

	public void deleteDosingPunpData(int position) {
		deleteData(DOSING_PUMP_TABLE, position);
	}

	public void deleteDosingPunpData(ArrayList<Integer> positions) {
		deleteData(DOSING_PUMP_TABLE, positions);
	}

	private void deleteData(String tableName, int position) {
		Cursor cursor = mSqLiteDatabase.query(tableName,
				new String[] { "_id" }, null, null, null, null, null);
		if (cursor == null) {
			return;
		}
		cursor.moveToPosition(position);
		int id = cursor.getInt(cursor.getColumnIndex("_id"));
		mSqLiteDatabase.delete(tableName, "_id = ?",
				new String[] { String.valueOf(id) });
		cursor.close();
	}

	private void deleteData(String tableName, ArrayList<Integer> positions) {
		String[] ids = new String[positions.size()];
		String whereClause = "_id = ?";
		Cursor cursor = mSqLiteDatabase.query(tableName,
				new String[] { "_id" }, null, null, null, null, null);
		if (cursor == null) {
			return;
		}
		for (int i = 0; i < positions.size(); i++) {
			cursor.moveToPosition(positions.get(i));
			ids[i] = String
					.valueOf(cursor.getInt(cursor.getColumnIndex("_id")));
			if (i > 0) {
				whereClause += " or _id = ?";
			}
		}
		mSqLiteDatabase.delete(tableName, whereClause, ids);
		cursor.close();
	}

	public void updateWaterMotorData(int position, WaterMotorData data) {
		updateData(WATER_MOTOR_TABLE, data, position);
	}

	public void updateProteinSkimmerData(int position, ProteinSkimmerData data) {
		updateData(PROTEIN_SKIMMER_TABLE, data, position);
	}

	public void updateUVLightData(int position, UVLightData data) {
		updateData(UV_LIGHT_TABLE, data, position);
	}

	public void updateDosingPunpData(int position, DosingPumpData data) {
		updateData(DOSING_PUMP_TABLE, data, position);
	}

	private void updateData(String tableName, ModuleData data, int position) {
		Cursor cursor = mSqLiteDatabase.query(tableName,
				new String[] { "_id" }, null, null, null, null, null);
		if (cursor == null) {
			return;
		}
		cursor.moveToPosition(position);
		int id = cursor.getInt(cursor.getColumnIndex("_id"));
		int i = mSqLiteDatabase.update(tableName, createModuleData(data),
				"_id = ?", new String[] { String.valueOf(id) });
		cursor.close();
	}

	public WaterMotorData queryWaterMotorData(Cursor c) {
		WaterMotorData data = new WaterMotorData();
		queryModuleData(data, c);
		data.isOn = c.getInt(c.getColumnIndex(ON_OFF)) == 1 ? true : false;

		return data;
	}

	public ProteinSkimmerData queryProteinSkimmerData(Cursor c) {
		ProteinSkimmerData data = new ProteinSkimmerData();
		queryModuleData(data, c);
		data.duration = c.getInt(c.getColumnIndex(DURATION));

		return data;
	}

	public UVLightData queryUVLightData(Cursor c) {
		UVLightData data = new UVLightData();
		queryModuleData(data, c);

		return data;
	}

	public DosingPumpData queryDosingPumpData(Cursor c) {
		DosingPumpData data = new DosingPumpData();
		data.isPump1On = c.getInt(c.getColumnIndex(PUMP_1_ON_OFF)) == 1 ? true
				: false;
		data.isPump2On = c.getInt(c.getColumnIndex(PUMP_2_ON_OFF)) == 1 ? true
				: false;
		data.isPump3On = c.getInt(c.getColumnIndex(PUMP_3_ON_OFF)) == 1 ? true
				: false;
		data.isPump4On = c.getInt(c.getColumnIndex(PUMP_4_ON_OFF)) == 1 ? true
				: false;
		data.pump1Dosing = c.getInt(c.getColumnIndex(PUMP_1_DOSING));
		data.pump2Dosing = c.getInt(c.getColumnIndex(PUMP_2_DOSING));
		data.pump3Dosing = c.getInt(c.getColumnIndex(PUMP_3_DOSING));
		data.pump4Dosing = c.getInt(c.getColumnIndex(PUMP_4_DOSING));

		queryModuleData(data, c);

		return data;
	}

	public ArrayList<WaterMotorData> queryWaterMotorDataList() {
		ArrayList<WaterMotorData> list = new ArrayList<WaterMotorData>();
		Cursor cursor = mSqLiteDatabase.query(WATER_MOTOR_TABLE, null, null,
				null, null, null, null);
		if (!cursor.moveToFirst()) {
			cursor.close();
			return list;
		}
		do {
			list.add(queryWaterMotorData(cursor));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	public ArrayList<ProteinSkimmerData> queryProteinSkimmerDataList() {
		ArrayList<ProteinSkimmerData> list = new ArrayList<ProteinSkimmerData>();
		Cursor cursor = mSqLiteDatabase.query(PROTEIN_SKIMMER_TABLE, null,
				null, null, null, null, null);
		if (!cursor.moveToFirst()) {
			cursor.close();
			return list;
		}
		do {
			list.add(queryProteinSkimmerData(cursor));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	public ArrayList<UVLightData> queryUVLightDataList() {
		ArrayList<UVLightData> list = new ArrayList<UVLightData>();
		Cursor cursor = mSqLiteDatabase.query(UV_LIGHT_TABLE, null, null, null,
				null, null, null);
		if (!cursor.moveToFirst()) {
			cursor.close();
			return list;
		}
		do {
			list.add(queryUVLightData(cursor));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	public ArrayList<DosingPumpData> queryDosingPumpDataList() {
		ArrayList<DosingPumpData> list = new ArrayList<DosingPumpData>();
		Cursor cursor = mSqLiteDatabase.query(DOSING_PUMP_TABLE, null, null,
				null, null, null, null);
		if (!cursor.moveToFirst()) {
			cursor.close();
			return list;
		}
		do {
			list.add(queryDosingPumpData(cursor));
		} while (cursor.moveToNext());
		cursor.close();
		return list;
	}

	private ModuleData queryModuleData(ModuleData data, Cursor c) {
		if (c == null) {
			return null;
		}
		if (data == null) {
			data = new ModuleData();
		}
		data.hour = c.getInt(c.getColumnIndex(HOUR));
		data.minute = c.getInt(c.getColumnIndex(MINUTE));
		data.isAM = c.getInt(c.getColumnIndex(AM_PM)) == 0 ? true : false;
		data.isMon = c.getInt(c.getColumnIndex(MON)) == 1 ? true : false;
		data.isTues = c.getInt(c.getColumnIndex(TUES)) == 1 ? true : false;
		data.isWed = c.getInt(c.getColumnIndex(WED)) == 1 ? true : false;
		data.isThur = c.getInt(c.getColumnIndex(THUR)) == 1 ? true : false;
		data.isFri = c.getInt(c.getColumnIndex(FRI)) == 1 ? true : false;
		data.isSat = c.getInt(c.getColumnIndex(SAT)) == 1 ? true : false;
		data.isSun = c.getInt(c.getColumnIndex(SUN)) == 1 ? true : false;

		return data;
	}

	private ContentValues createModuleData(ModuleData data) {
		ContentValues values = new ContentValues();
		values.put(HOUR, data.hour);
		values.put(MINUTE, data.minute);
		values.put(AM_PM, data.isAM ? 0 : 1);
		values.put(MON, data.isMon ? 1 : 0);
		values.put(TUES, data.isTues ? 1 : 0);
		values.put(WED, data.isWed ? 1 : 0);
		values.put(THUR, data.isThur ? 1 : 0);
		values.put(FRI, data.isFri ? 1 : 0);
		values.put(SAT, data.isSat ? 1 : 0);
		values.put(SUN, data.isSun ? 1 : 0);
		if (data instanceof WaterMotorData) {
			values.put(ON_OFF, ((WaterMotorData) data).isOn ? 1 : 0);
		} else if (data instanceof ProteinSkimmerData) {
			values.put(DURATION, ((ProteinSkimmerData) data).duration);
		} else if (data instanceof DosingPumpData) {
			values.put(PUMP_1_ON_OFF, ((DosingPumpData) data).isPump1On ? 1 : 0);
			values.put(PUMP_2_ON_OFF, ((DosingPumpData) data).isPump2On ? 1 : 0);
			values.put(PUMP_3_ON_OFF, ((DosingPumpData) data).isPump3On ? 1 : 0);
			values.put(PUMP_4_ON_OFF, ((DosingPumpData) data).isPump4On ? 1 : 0);
			values.put(PUMP_1_DOSING, ((DosingPumpData) data).pump1Dosing);
			values.put(PUMP_2_DOSING, ((DosingPumpData) data).pump2Dosing);
			values.put(PUMP_3_DOSING, ((DosingPumpData) data).pump3Dosing);
			values.put(PUMP_4_DOSING, ((DosingPumpData) data).pump4Dosing);
		}
		return values;
	}

	public int[] getTime(Cursor c) {
		int[] time = new int[2];
		if (c == null) {
			return time;
		}
		time[0] = c.getInt(c.getColumnIndex(HOUR));
		time[1] = c.getInt(c.getColumnIndex(MINUTE));

		return time;
	}

	public int getProteinSkimmerDuration(Cursor c) {
		int duration;
		if (c == null) {
			return -1;
		}
		duration = c.getInt(c.getColumnIndex(DURATION));
		return duration;
	}

	public boolean[] getLaunchPump(Cursor c) {
		boolean[] bools = new boolean[4];
		if (c == null) {
			return bools;
		}
		bools[0] = c.getInt(c.getColumnIndex(PUMP_1_ON_OFF)) == 1 ? true
				: false;
		bools[1] = c.getInt(c.getColumnIndex(PUMP_2_ON_OFF)) == 1 ? true
				: false;
		bools[2] = c.getInt(c.getColumnIndex(PUMP_3_ON_OFF)) == 1 ? true
				: false;
		bools[3] = c.getInt(c.getColumnIndex(PUMP_4_ON_OFF)) == 1 ? true
				: false;
		return bools;
	}

	public boolean isAm(Cursor c) {
		boolean isAm = true;
		if (c == null) {
			return isAm;
		}
		isAm = c.getInt(c.getColumnIndex(AM_PM)) == 0 ? true : false;

		return isAm;
	}

	public boolean isMonSet(Cursor c) {
		return isWeekSet(c, MON);
	}

	public boolean isTuesSet(Cursor c) {
		return isWeekSet(c, TUES);
	}

	public boolean isWedSet(Cursor c) {
		return isWeekSet(c, WED);
	}

	public boolean isThurSet(Cursor c) {
		return isWeekSet(c, THUR);
	}

	public boolean isFriSet(Cursor c) {
		return isWeekSet(c, FRI);
	}

	public boolean isSatSet(Cursor c) {
		return isWeekSet(c, SAT);
	}

	public boolean isSunSet(Cursor c) {
		return isWeekSet(c, SUN);
	}

	public boolean isWaterMotorOn(Cursor c) {
		if (c == null) {
			return false;
		}
		return c.getInt(c.getColumnIndex(ON_OFF)) == 1 ? true : false;
	}

	public Cursor getCursor(String tableName) {
		Cursor cursor = mSqLiteDatabase.query(tableName, null, null, null,
				null, null, null);
		return cursor;
	}

	public Cursor getCursor(String tableName, int position) {
		Cursor cursor = mSqLiteDatabase.query(tableName, null, null, null,
				null, null, null);
		cursor.moveToPosition(position);
		return cursor;
	}

	private boolean isWeekSet(Cursor c, String column) {
		if (c == null) {
			return false;
		}
		boolean result = c.getInt(c.getColumnIndex(column)) == 1 ? true : false;

		return result;
	}
}
